

-- Function para cargar movimientos de ventas bajados desde portaliva de afip ----


CREATE OR REPLACE FUNCTION afip.portaliva_ventas(character varying, integer, integer, character varying, integer)
  RETURNS integer AS
$BODY$
DECLARE
prubpad       alias for $1; --rubro de padron
pidcaja       alias for $2; -- id caja
pidafipeqv    alias for $3; -- id grupo de equivalencia fiscal
pcuit         alias for $4; -- para los sistemas en la nube cuit para subir archivos a la nube 
pruta         alias for $5; -- servidor: 1: local, 2: hosting, 3: datacenter

vcadena 	character varying;
vservidor character varying;
ccompras      REFCURSOR;
calicuotas    REFCURSOR;
vrenglon      varchar(500);
vtipocom      character varying(4);
vtipocomges   character varying(4);
vcont         int4;
vcontequiv    int4;
vbueno        varchar(1);
vidpad        int4;
vcuit         character varying(20);
vletra        varchar(1);
vptovta       int4;
vnro          int4;
vfecha        timestamp;
vvto        timestamp;
vimpototal    character varying(16);
vnombpad      character varying(150);
vcoddoc       character varying(2);

vcodmoneda    character varying(3);
vtipocambio   character varying(10);

vnetogravado  character varying(16);
valicuota     int4;
vimpoiva      character varying(16);
vtasaiva      numeric(16,2);
vidcon        int4;
vsigno        int4;
vsignoori     int4;

ptovta        character varying(5);
numero        character varying(20);
numerohasta   character varying(20);

vidctbcpra     int4;
vidctbiibb    int4; 
vidctbinac    int4;
vidctbimun    int4;
vidctbiint    int4;
vidctbotrasper  int4;
vimpupad      int4;

vimponogravado character varying(16); 
vimpoexentas   character varying(16);
vimpoperciva   character varying(16);
vimpoiibb      character varying(16);
vimpoinac      character varying(16);
vimpoimun      character varying(16);
vimpoiint      character varying(16);
vimponocateg   character varying(16);
vimpootrasper  character varying(16);
vimpo_netog_0    character varying(16);
vimpo_netog_2  character varying(16);
vimpo_iva_2    character varying(16);
vimpo_netog_5  character varying(16);
vimpo_iva_5    character varying(16);
vimpo_netog_10 character varying(16);
vimpo_iva_10   character varying(16);
vimpo_netog_21 character varying(16);
vimpo_iva_21   character varying(16);
vimpo_netog_27 character varying(16);
vimpo_iva_27   character varying(16);
vimpo_netog_total character varying(16);
vimpo_iva_total character varying(16);
vimpotrasper   character varying(16);
acumgravado   numeric(16,2);
vtotal        numeric(15,2);
vimpoaux      numeric(15,2);
vivacalculado  numeric(15,2);
vivadif       numeric(15,2);

vmiva         numeric(15,2);
vdife         numeric(15,2);
vnombsuc      character varying(30);
vidciu        int4;
vcpos         character varying(4);
vcuitaux      character varying(13);
vcodpadr      character varying(20);
vcuitges      character varying(13);

vMensajeError  TEXT;
miEnter	       TEXT;

vNroXml       integer;


BEGIN
CREATE TABLE afip.portaliva_ventas
(
fecha character varying(10),
tipocomprobante character varying(3),
nroptovta character varying(5),
nro character varying(20),

nrohasta character varying(20),

coddocumento character varying(2),
cuit character varying(20),
nombre character varying(150),

vto  character varying(10),

impo_total character varying(15), 
codmoneda character varying(3),
tipocambio character varying(10),
impo_nogravado character varying(15),
impo_exento character varying(15),
impo_perc_nacionales character varying(15),
impo_perc_iibb character varying(15),
impo_perc_municipales character varying(15),

impo_perc_nocateg character varying(15),
impo_imp_internos character varying(15),
impo_otrostributos character varying(15),
impo_netog_0 character varying(15),
impo_netog_2 character varying(15),
impo_iva_2 character varying(15),
impo_netog_5 character varying(15),
impo_iva_5 character varying(15),
impo_netog_10 character varying(15),
impo_iva_10 character varying(15),
impo_netog_21 character varying(15),
impo_iva_21 character varying(15),
impo_netog_27 character varying(15),
impo_iva_27 character varying(15),
impo_netog_total character varying(15),
impo_iva_total character varying(15),
signo integer,
tipocomges character varying(4),
cuitges character varying(13),
idpad integer 
)
WITH ( OIDS=TRUE );
ALTER TABLE afip.portaliva_ventas OWNER TO abacouser;

CREATE TABLE afip.portaliva_errores
(error character varying(500)
)
WITH (OIDS=TRUE);
ALTER TABLE afip.portaliva_errores OWNER TO abacouser;


CREATE TABLE cliente.renglones
(
  renglon character varying(1000)
)
WITH (OIDS=TRUE);
ALTER TABLE cliente.renglones OWNER TO abacouser;

-- se carga la tabla renglones con el archivo bajado de afip
if pruta = 1 then
   copy cliente.renglones from 'c:/abaco/gestion/importar/portaliva_ventas.csv';
  else
    if pruta = 2 then
       vservidor:= '/var/www/html/implementacion_datos/clientes/'; -- Hosting
     else
       vservidor:= '/datacenter/var/www/html/implementacion_datos/clientes/'; -- Datacenter (fibercorp)
    end if;
    vcadena:= 'copy cliente.renglones from ''' || vservidor || pcuit || '/' || 'portaliva_ventas.csv' || '''';
    EXECUTE vcadena; 
end if;

-- se borra el renglon de titulos
delete from cliente.renglones where paso_a_minusculas(substring(split_part(renglon, ';',1), 1, 5)) = 'Fecha';

-- se carga la tabla afip.portaliva_ventas desde renglones 
insert into afip.portaliva_ventas
select  
       split_part(renglon,';',1) as fecha,
       lpad(split_part(renglon,';',2), 3, '0') as tipocomprobante,
       split_part(renglon,';',3) as nroptovta,
       split_part(renglon,';',4) as nro,
       split_part(renglon,';',5) as nrohasta,
       split_part(renglon,';',6) as coddocumento,
       split_part(renglon,';',7) as cuit,
       split_part(renglon,';',8) as nombre,
       split_part(renglon,';',9) as vto, 
       split_part(renglon,';',10) as impo_total, 
       split_part(renglon,';',11) as codmoneda,
       split_part(renglon,';',12) as tipocambio,
       split_part(renglon,';',13) as impo_nogravado,
       split_part(renglon,';',14) as impo_exento,
       split_part(renglon,';',15) as impo_perc_nacionales,
       split_part(renglon,';',16) as impo_perc_iibb,
       split_part(renglon,';',17) as impo_perc_municipales,
       split_part(renglon,';',18) as impo_perc_nocateg,
       split_part(renglon,';',19) as impo_imp_internos,
       split_part(renglon,';',20) as impo_otrostributos,
       split_part(renglon,';',21) as impo_netog_0,
       split_part(renglon,';',22) as impo_netog_2,
       split_part(renglon,';',23) as impo_iva_2,
       split_part(renglon,';',24) as impo_netog_5,
       split_part(renglon,';',25) as impo_iva_5,
       split_part(renglon,';',26) as impo_netog_10,
       split_part(renglon,';',27) as impo_iva_10,
       split_part(renglon,';',28) as impo_netog_21,
       split_part(renglon,';',29) as impo_iva_21,
       split_part(renglon,';',30) as impo_netog_27,
       split_part(renglon,';',31) as impo_iva_27,
       split_part(renglon,';',32) as impo_netog_total,
       split_part(renglon,';',33)as impo_iva_total,
       1 as signo,
       null,
       null, 
       null
from cliente.renglones;

update afip.portaliva_ventas set signo = -1 where (str_a_nro(impo_total) < 0);

-- se setean en '0.00' los campos vacios correspondientes a importes
update afip.portaliva_ventas set impo_total = '0.00' where trim(impo_total) = '';
update afip.portaliva_ventas set impo_nogravado = '0.00' where trim(impo_nogravado) = '';
update afip.portaliva_ventas set impo_exento = '0.00' where trim(impo_exento) = '';

update afip.portaliva_ventas set impo_perc_nacionales = '0.00' where trim(impo_perc_nacionales) = '';
update afip.portaliva_ventas set impo_perc_iibb = '0.00' where trim(impo_perc_iibb) = '';
update afip.portaliva_ventas set impo_perc_municipales = '0.00' where trim(impo_perc_municipales) = '';
update afip.portaliva_ventas set impo_perc_nocateg = '0.00' where trim(impo_perc_nocateg) = '';
update afip.portaliva_ventas set impo_imp_internos = '0.00' where trim(impo_imp_internos) = '';
update afip.portaliva_ventas set impo_otrostributos = '0.00' where trim(impo_otrostributos) = ''; 
update afip.portaliva_ventas set impo_netog_0 = '0.00' where trim(impo_netog_0) = '';
update afip.portaliva_ventas set impo_netog_2 = '0.00' where trim(impo_netog_2) = '';
update afip.portaliva_ventas set impo_iva_2 = '0.00' where trim(impo_iva_2) = '';
update afip.portaliva_ventas set impo_netog_5 = '0.00' where trim(impo_netog_5) = '';
update afip.portaliva_ventas set impo_iva_5 = '0.00' where trim(impo_iva_5) = '';
update afip.portaliva_ventas set impo_netog_10 = '0.00' where trim(impo_netog_10) = '';
update afip.portaliva_ventas set impo_iva_10 = '0.00' where trim(impo_iva_10) = '';
update afip.portaliva_ventas set impo_netog_21 = '0.00' where trim(impo_netog_21) = '';
update afip.portaliva_ventas set impo_iva_21 = '0.00' where trim(impo_iva_21) = '';
update afip.portaliva_ventas set impo_netog_27 = '0.00' where trim(impo_netog_27) = ''; 
update afip.portaliva_ventas set impo_iva_27 = '0.00' where trim(impo_iva_27) = '';
update afip.portaliva_ventas set impo_netog_total = '0.00' where trim(impo_netog_total) = ''; 
update afip.portaliva_ventas set impo_iva_total = '0.00' where trim(impo_iva_total) = '';

-- se setea el tipo de comprobante de gestión para esa equivalencia de afip
update afip.portaliva_ventas set tipocomges= (select tipocom from afipeqvcom 
                                                where codificacion = tipocomprobante and idafipeqv = pidafipeqv limit 1);

update afip.portaliva_ventas set cuitges= trim(cast(cast(str_a_nro(cuit) as numeric(11,0)) as char(11)));

update afip.portaliva_ventas set cuitges= substring(cuitges, 1, 2) || '-' || substring(cuitges, 3, 8) || '-' || substring(cuitges, 11, 1);

-- se recupera el idpad de gestion correspondiente al cuit de ese movimiento
update afip.portaliva_ventas set idpad= (select id from pad where cuit = cuitges and substring(codpadr, 1, length(prubpad)) = prubpad limit 1);

-- se recuperan los id de las cuentas contables definidas en la configuracion (afipctb) para el registro tipo ventas
select idctbmov, idctbiibb, idctbinac, idctbimun, idctbiint, idctbotrasper from afipctb where tiporeg = 'V'
into vidctbcpra, vidctbiibb, vidctbinac, vidctbimun, vidctbiint, vidctbotrasper; 

-- Se buscan los cuit que no existen en el sistema para informarlos y se aborta el proceso
miEnter:=  chr(13);

select count(*) into vcont from afip.portaliva_ventas where idpad is null;

if vcont > 0 then
   insert into afip.portaliva_errores(error) 
   (select 'No existe ninguna entidad con cuit '  || cuitges || ' para el rubro ' || prubpad
   from afip.portaliva_ventas where idpad is null); 

   vMensajeError:= 'Hay cuits que no existen en el sistema. Ver archivo "errores_cuit.csv" y dar de alta esos cuits' || miEnter;
   vMensajeError:= vMensajeError || (select array_to_string(array(select error from afip.portaliva_errores), miEnter));

   if pruta = 1 then
       copy  (select error FROM afip.portaliva_errores) to 'c:/abaco/gestion/importar/errores_cuit.csv'; 
     else 
       vcadena:= 'copy  (select error FROM afip.portaliva_errores) to ''' || vservidor || pcuit || '/' || 'errores_cuit.csv' || '''';
       EXECUTE vcadena; 
   end if;
   RAISE EXCEPTION '%', vMensajeError;
 else
   if pruta = 1 then
       copy (select ' ' FROM afip.portaliva_errores) to 'c:/abaco/gestion/importar/errores_cuit.csv'; 
     else   
       vcadena:= 'copy (select '' FROM afip.portaliva_errores) to ''' || vservidor || pcuit || '/' || 'errores_cuit.csv' || '''';
       EXECUTE vcadena; 
   end if; 
end if;   

-- Se informan los tipos de comprobantes que no tienen equivalencia en gestión, esos no se incorporan, pero el resto si.
select count(*) into vcontequiv from afip.portaliva_ventas where tipocomges is null;

if vcontequiv > 0 then
   insert into afip.portaliva_errores(error) 
   select 'No existe ningun tipo de comprobante en gestion correspondiente al '  || tipocomprobante || ' de afip '
   from afip.portaliva_ventas where tipocomges is null; 

   if pruta = 1 then
       copy (select 'No existe ningun tipo de comprobante en gestion correspondiente al '  || tipocomprobante || ' de afip '
       from afip.portaliva_ventas where tipocomges is null) to 'c:/abaco/gestion/importar/errores_equivalencias.csv'; 
     else
       vcadena:= 'copy  (select error FROM afip.portaliva_errores) to ''' || vservidor || pcuit || '/' || 'errores_equivalencias.csv' || '''';
       EXECUTE vcadena; 
   end if;
 else
   if pruta = 1 then
      copy  (select '' FROM afip.portaliva_errores) to 'c:/abaco/gestion/importar/errores_equivalencias.csv'; 
    else
       vcadena:= 'copy  (select '' FROM afip.portaliva_errores) to ''' || vservidor || pcuit || '/' || 'errores_equivalencias.csv' || '''';
       EXECUTE vcadena; 
   end if;
end if;   

vNroXml:= ( SELECT NEXTVAL('importacion.sec_next_nroxml') );
perform importacion.impxml_add(vNroXml, '<Movimientos>');

open ccompras FOR (SELECT fecha, tipocomprobante, nroptovta, nro, nrohasta, coddocumento, cuit, nombre, vto, impo_total, codmoneda, tipocambio,
                          impo_nogravado, impo_exento, impo_perc_nacionales, impo_perc_iibb,  
                          impo_perc_municipales, impo_perc_nocateg, impo_imp_internos, impo_otrostributos, impo_netog_0,
                          impo_netog_2, impo_iva_2, impo_netog_5, impo_iva_5, impo_netog_10, impo_iva_10, 
                          impo_netog_21, impo_iva_21, impo_netog_27, impo_iva_27, impo_netog_total, impo_iva_total, signo,
                          tipocomges, cuitges, idpad 
                   from afip.portaliva_ventas);
LOOP
FETCH ccompras INTO vfecha, vtipocom, ptovta, numero, numerohasta, vcoddoc, vcuit, vnombpad, vvto, vimpototal, vcodmoneda, vtipocambio,
                    vimponogravado, vimpoexentas, vimpoinac, vimpoiibb, 
                    vimpoimun, vimponocateg, vimpoiint, vimpootrasper, vimpo_netog_0,
                    vimpo_netog_2, vimpo_iva_2, vimpo_netog_5, vimpo_iva_5, vimpo_netog_10, vimpo_iva_10, 
                    vimpo_netog_21, vimpo_iva_21, vimpo_netog_27, vimpo_iva_27, vimpo_netog_total, vimpo_iva_total, vsigno,
                    vtipocomges, vcuitges, vidpad; 
                    
EXIT WHEN (NOT FOUND);
     vptovta:= ptovta;  
     vnro:= numero;
     vbueno:= 'S';

     select letra from afipeqvcom into vletra
     where codificacion = vtipocom and idafipeqv = pidafipeqv limit 1;
         
     if vtipocomges is null then
        vbueno = 'N';
     end if;     


-- se reemplazan las comas por punto para los campos de importe

    vimpototal:= replace(vimpototal, ',', '.');
    vimponogravado:= replace(vimponogravado, ',', '.');
    vimpoexentas:= replace(vimpoexentas, ',', '.');  
    vimponocateg:= replace(vimpoperciva, ',', '.');   
    vimpoiibb:= replace(vimpoiibb, ',', '.');     
    vimpoinac:= replace(vimpoinac, ',', '.');     
    vimpoimun:= replace(vimpoimun, ',', '.');     
    vimpoiint:= replace(vimpoiint, ',', '.');     
    vimpootrasper:= replace(vimpootrasper, ',', '.'); 
    vimpo_netog_0:= replace(vimpo_netog_0, ',', '.');   
    vimpo_netog_2:= replace(vimpo_netog_2, ',', '.');  
    vimpo_iva_2:= replace(vimpo_iva_2, ',', '.');   
    vimpo_netog_5:= replace(vimpo_netog_5, ',', '.');  
    vimpo_iva_5:= replace(vimpo_iva_5, ',', '.');   
    vimpo_netog_10:= replace(vimpo_netog_10, ',', '.'); 
    vimpo_iva_10:= replace(vimpo_iva_10, ',', '.');  
    vimpo_netog_21:= replace(vimpo_netog_21, ',', '.');
    vimpo_iva_21:= replace(vimpo_iva_21, ',', '.');  
    vimpo_netog_27:= replace(vimpo_netog_27, ',', '.');
    vimpo_iva_27:= replace(vimpo_iva_27, ',', '.');   
    vimpo_netog_total:= replace(vimpo_netog_total, ',', '.'); 
    vimpo_iva_total:= replace(vimpo_iva_total, ',', '.');

    vimpototal:= replace(vimpototal, '-', '');
    vimponogravado:= replace(vimponogravado, '-', '');
    vimpoexentas:= replace(vimpoexentas, '-', '');  
    vimponocateg:= replace(vimpoperciva, '-', '');   
    vimpoiibb:= replace(vimpoiibb, '-', '');     
    vimpoinac:= replace(vimpoinac, '-', '');     
    vimpoimun:= replace(vimpoimun, '-', '');     
    vimpoiint:= replace(vimpoiint, '-', '');     
    vimpootrasper:= replace(vimpootrasper, '-', ''); 
    vimpo_netog_0:= replace(vimpo_netog_0, '-', '');   
    vimpo_netog_2:= replace(vimpo_netog_2, '-', '');  
    vimpo_iva_2:= replace(vimpo_iva_2, '-', '');   
    vimpo_netog_5:= replace(vimpo_netog_5, '-', '');  
    vimpo_iva_5:= replace(vimpo_iva_5, '-', '');   
    vimpo_netog_10:= replace(vimpo_netog_10, '-', ''); 
    vimpo_iva_10:= replace(vimpo_iva_10, '-', '');  
    vimpo_netog_21:= replace(vimpo_netog_21, '-', '');
    vimpo_iva_21:= replace(vimpo_iva_21, '-', '');  
    vimpo_netog_27:= replace(vimpo_netog_27, '-', '');
    vimpo_iva_27:= replace(vimpo_iva_27, '-', '');   
    vimpo_netog_total:= replace(vimpo_netog_total, '-', ''); 
    vimpo_iva_total:= replace(vimpo_iva_total, '-', '');


-- Se busca si hay alguna imputacion especial de padrón para redefinir la cuenta de compras. 
-- Si no hay ninguna se deja la recuperada de la tabla afictbde afictb 
    vimpupad:= null;
    select idctb from afip.portaliva_padimp where tipomov = 'V' and idpad = cast(vidpad as integer) into vimpupad;

    if vimpupad is null then
       select codpadr from pad where id = cast(vidpad as integer) into vcodpadr;
       select idctb from afip.portaliva_padimp where tipomov = 'V' and vcodpadr like (codpadr || '%')
       order by codpadr desc limit 1 into vimpupad;
    end if;   

    if vimpupad is not null then
       vidctbcpra:= vimpupad; 
    end if;   

    vimpoaux:= 0;
    vivadif:= 0;
    if vbueno = 'S' then     
        perform importacion.impxml_add(vNroXml, '<Comprobante TipoCom= "' || vtipocomges || '">');
        perform importacion.impxml_add(vNroXml, '<Encabezado>');

        perform importacion.impxml_add(vNroXml, '<CodCuit Campo= "IdPad" RubroPad= "' || prubpad || '">' || vcuitaux || '</CodCuit>');
        perform importacion.impxml_add(vNroXml, '<IdPad>' || cast(vidpad as integer) || '</IdPad>');
        perform importacion.impxml_add(vNroXml, '<Letra>' || vletra || '</Letra>');
        perform importacion.impxml_add(vNroXml, '<NroPtoVta>' || cast(vptovta as integer) || '</NroPtoVta>');
        perform importacion.impxml_add(vNroXml, '<Nro>' || cast(vnro as integer) || '</Nro>');
        perform importacion.impxml_add(vNroXml, '<Fecha>' || to_char(cast(vFecha as date),'DD/MM/YYYY') || '</Fecha>');
        perform importacion.impxml_add(vNroXml, '</Encabezado>');
        perform importacion.impxml_add(vNroXml, '<Renglones>');

        if vsigno < 0  then
           perform importacion.impxml_add(vNroXml, '<RenglonFondo Signo = "Salida">');
         else
           perform importacion.impxml_add(vNroXml, '<RenglonFondo Signo = "Entrada">');
        end if;
        perform importacion.impxml_add(vNroXml, '<IdPad>' || cast(pidcaja as integer) ||'</IdPad>');
        perform importacion.impxml_add(vNroXml, '<TipoValT>EFE-M</TipoValT>');
        perform importacion.impxml_add(vNroXml, '<TipoAcc>I</TipoAcc>');
        perform importacion.impxml_add(vNroXml, '<CodPadRAcc>F.CAJ</CodPadRAcc>');
        perform importacion.impxml_add(vNroXml, '<ImpoMoneda>' || cast(vimpototal as numeric(15,2)) || '</ImpoMoneda>');
        perform importacion.impxml_add(vNroXml, '</RenglonFondo>');
        vtotal:= cast(vimpototal as numeric(15,2));
---ivas y netos
        if cast(vimpo_netog_0 as numeric(15,2)) <> 0  then
           if vsigno < 0 then
              perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Entrada">');
            else
              perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Salida">');
           end if; 
           perform importacion.impxml_add(vNroXml, '<IdCtb>' || vidctbcpra || '</IdCtb>'); 
           perform importacion.impxml_add(vNroXml, '<ImpocIva>' || cast(vimpo_netog_0 as numeric(15,2)) || '</ImpocIva>'); 
           perform importacion.impxml_add(vNroXml, '<Impo>' || cast(vimpo_netog_0 as numeric(15,2)) || '</Impo>'); 
           perform importacion.impxml_add(vNroXml, '<Piva>' || cast('0.00' as numeric(16, 2)) || '</Piva>');
           perform importacion.impxml_add(vNroXml, '</RenglonContable>');
           vimpoaux:= vimpoaux + cast(vimpo_netog_0 as numeric(15,2)); 
        end if; 

        if cast(vimpo_netog_2 as numeric(15,2)) <> 0  then
           vtasaiva:= 2.50;
           select concep.id from conauto join concep on conauto.idcon = concep.id
           where tipocom = vtipocomges and concep.afiptipo = 'IVA' and concep.ivainfmov = vtasaiva and concep.ivasupmov = vtasaiva
           limit 1 into vidcon;
           if vidcon is not null then
                if vsigno < 0 then
                   perform importacion.impxml_add(vNroXml, '<RenglonConcepto Signo = "Entrada">');
                 else
                   perform importacion.impxml_add(vNroXml, '<RenglonConcepto Signo = "Salida">');
                end if; 
                perform importacion.impxml_add(vNroXml, '<IdCon>' || cast(vidcon as integer) ||'</IdCon>');
                perform importacion.impxml_add(vNroXml, '<Alicuota>' || cast(vtasaiva as numeric(16, 2)) || '</Alicuota>');
                perform importacion.impxml_add(vNroXml, '<Impo>' || cast(vimpo_iva_2 as numeric(15,2)) || '</Impo>');
                perform importacion.impxml_add(vNroXml, '</RenglonConcepto>');
                vimpoaux:= vimpoaux + cast(vimpo_iva_2 as numeric(15,2));
                if vsigno < 0 then
                   perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Entrada">');
                 else
                   perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Salida">');
                end if; 
                perform importacion.impxml_add(vNroXml, '<IdCtb>' || vidctbcpra || '</IdCtb>'); 
                perform importacion.impxml_add(vNroXml, '<ImpocIva>' || cast(vimpo_netog_2 as numeric(15,2)) + cast(vimpo_iva_2 as numeric(15,2))|| '</ImpocIva>'); 
                perform importacion.impxml_add(vNroXml, '<Impo>' || cast(vimpo_netog_2 as numeric(15,2)) || '</Impo>'); 
                perform importacion.impxml_add(vNroXml, '<Piva>' || cast(vtasaiva as numeric(16, 2)) || '</Piva>');
                perform importacion.impxml_add(vNroXml, '</RenglonContable>');
                vimpoaux:= vimpoaux + cast(vimpo_netog_2 as numeric(15,2)); 
                vivacalculado:= round((cast(vimpo_netog_2 as numeric(15,2)) * vtasaiva / 100), 2);
                vivadif:= vivadif + (vivacalculado - cast(vimpo_iva_2 as numeric(15,2)));

           end if;
        end if;

        if cast(vimpo_netog_5 as numeric(15,2)) <> 0  then
           vtasaiva:= 5.00;
           select concep.id from conauto join concep on conauto.idcon = concep.id
           where tipocom = vtipocomges and concep.afiptipo = 'IVA' and concep.ivainfmov = vtasaiva and concep.ivasupmov = vtasaiva
           limit 1 into vidcon;
           if vidcon is not null then
                if vsigno < 0 then
                   perform importacion.impxml_add(vNroXml, '<RenglonConcepto Signo = "Entrada">');
                 else
                   perform importacion.impxml_add(vNroXml, '<RenglonConcepto Signo = "Salida">');
                end if; 
                perform importacion.impxml_add(vNroXml, '<IdCon>' || cast(vidcon as integer) ||'</IdCon>');
                perform importacion.impxml_add(vNroXml, '<Alicuota>' || cast(vtasaiva as numeric(16, 2)) || '</Alicuota>');
                perform importacion.impxml_add(vNroXml, '<Impo>' || cast(vimpo_iva_5 as numeric(15,2)) || '</Impo>');
                perform importacion.impxml_add(vNroXml, '</RenglonConcepto>');
                vimpoaux:= vimpoaux + cast(vimpo_iva_5 as numeric(15,2));
                if vsigno < 0 then
                   perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Entrada">');
                 else
                   perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Salida">');
                end if; 
                perform importacion.impxml_add(vNroXml, '<IdCtb>' || vidctbcpra || '</IdCtb>'); 
                perform importacion.impxml_add(vNroXml, '<ImpocIva>' || cast(vimpo_netog_5 as numeric(15,2)) + cast(vimpo_iva_5 as numeric(15,2))|| '</ImpocIva>'); 
                perform importacion.impxml_add(vNroXml, '<Impo>' || cast(vimpo_netog_5 as numeric(15,2)) || '</Impo>'); 
                perform importacion.impxml_add(vNroXml, '<Piva>' || cast(vtasaiva as numeric(16, 2)) || '</Piva>');
                perform importacion.impxml_add(vNroXml, '</RenglonContable>');
                vimpoaux:= vimpoaux + cast(vimpo_netog_5 as numeric(15,2));
                vivacalculado:= round((cast(vimpo_netog_5 as numeric(15,2)) * vtasaiva / 100), 2);
                vivadif:= vivadif + (vivacalculado - cast(vimpo_iva_5 as numeric(15,2)));
           end if;
        end if;


        if cast(vimpo_netog_10 as numeric(15,2)) <> 0  then
           vtasaiva:= 10.50;
           select concep.id from conauto join concep on conauto.idcon = concep.id
           where tipocom = vtipocomges and concep.afiptipo = 'IVA' and concep.ivainfmov = vtasaiva and concep.ivasupmov = vtasaiva
           limit 1 into vidcon;
           if vidcon is not null then
                if vsigno < 0 then
                   perform importacion.impxml_add(vNroXml, '<RenglonConcepto Signo = "Entrada">');
                 else
                   perform importacion.impxml_add(vNroXml, '<RenglonConcepto Signo = "Salida">');
                end if; 
                perform importacion.impxml_add(vNroXml, '<IdCon>' || cast(vidcon as integer) ||'</IdCon>');
                perform importacion.impxml_add(vNroXml, '<Alicuota>' || cast(vtasaiva as numeric(16, 2)) || '</Alicuota>');
                perform importacion.impxml_add(vNroXml, '<Impo>' || cast(vimpo_iva_10 as numeric(15,2)) || '</Impo>');
                perform importacion.impxml_add(vNroXml, '</RenglonConcepto>');
                vimpoaux:= vimpoaux + cast(vimpo_iva_10 as numeric(15,2));
                if vsigno < 0 then
                   perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Entrada">');
                 else
                   perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Salida">');
                end if; 
                perform importacion.impxml_add(vNroXml, '<IdCtb>' || vidctbcpra || '</IdCtb>'); 
                perform importacion.impxml_add(vNroXml, '<ImpocIva>' || cast(vimpo_netog_10 as numeric(15,2)) + cast(vimpo_iva_10 as numeric(15,2))|| '</ImpocIva>'); 
                perform importacion.impxml_add(vNroXml, '<Impo>' || cast(vimpo_netog_10 as numeric(15,2)) || '</Impo>'); 
                perform importacion.impxml_add(vNroXml, '<Piva>' || cast(vtasaiva as numeric(16, 2)) || '</Piva>');
                perform importacion.impxml_add(vNroXml, '</RenglonContable>');
                vimpoaux:= vimpoaux + cast(vimpo_netog_10 as numeric(15,2));
                vivacalculado:= round((cast(vimpo_netog_10 as numeric(15,2)) * vtasaiva / 100), 2);
                vivadif:= vivadif + (vivacalculado - cast(vimpo_iva_10 as numeric(15,2)));
           end if;
        end if;

        if cast(vimpo_netog_21 as numeric(15,2)) <> 0  then
           vtasaiva:= 21.00;
           select concep.id from conauto join concep on conauto.idcon = concep.id
           where tipocom = vtipocomges and concep.afiptipo = 'IVA' and concep.ivainfmov = vtasaiva and concep.ivasupmov = vtasaiva
           limit 1 into vidcon;
           if vidcon is not null then
              if vsigno < 0 then
                   perform importacion.impxml_add(vNroXml, '<RenglonConcepto Signo = "Entrada">');
                 else
                   perform importacion.impxml_add(vNroXml, '<RenglonConcepto Signo = "Salida">');
                end if; 
                perform importacion.impxml_add(vNroXml, '<IdCon>' || cast(vidcon as integer) ||'</IdCon>');
                perform importacion.impxml_add(vNroXml, '<Alicuota>' || cast(vtasaiva as numeric(16, 2)) || '</Alicuota>');
                perform importacion.impxml_add(vNroXml, '<Impo>' || cast(vimpo_iva_21 as numeric(15,2)) || '</Impo>');
                perform importacion.impxml_add(vNroXml, '</RenglonConcepto>');
                vimpoaux:= vimpoaux + cast(vimpo_iva_21 as numeric(15,2)); 
                if vsigno < 0 then
                   perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Entrada">');
                 else
                   perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Salida">');
                end if; 
                perform importacion.impxml_add(vNroXml, '<IdCtb>' || vidctbcpra || '</IdCtb>'); 
                perform importacion.impxml_add(vNroXml, '<ImpocIva>' || cast(vimpo_netog_21 as numeric(15,2)) + cast(vimpo_iva_21 as numeric(15,2))|| '</ImpocIva>'); 
                perform importacion.impxml_add(vNroXml, '<Impo>' || cast(vimpo_netog_21 as numeric(15,2)) || '</Impo>'); 
                perform importacion.impxml_add(vNroXml, '<Piva>' || cast(vtasaiva as numeric(16, 2)) || '</Piva>');
                perform importacion.impxml_add(vNroXml, '</RenglonContable>');
                vimpoaux:= vimpoaux + cast(vimpo_netog_21 as numeric(15,2));
                vivacalculado:= round((cast(vimpo_netog_21 as numeric(15,2)) * vtasaiva / 100), 2);
                vivadif:= vivadif + (vivacalculado - cast(vimpo_iva_21 as numeric(15,2)));
           end if;
        end if;

        if cast(vimpo_netog_27 as numeric(15,2)) <> 0  then
           vtasaiva:= 27.00;
           select concep.id from conauto join concep on conauto.idcon = concep.id
           where tipocom = vtipocomges and concep.afiptipo = 'IVA' and concep.ivainfmov = vtasaiva and concep.ivasupmov = vtasaiva
           limit 1 into vidcon;
           if vidcon is not null then
                if vsigno < 0 then
                   perform importacion.impxml_add(vNroXml, '<RenglonConcepto Signo = "Entrada">');
                 else
                   perform importacion.impxml_add(vNroXml, '<RenglonConcepto Signo = "Salida">');
                end if; 
                perform importacion.impxml_add(vNroXml, '<IdCon>' || cast(vidcon as integer) ||'</IdCon>');
                perform importacion.impxml_add(vNroXml, '<Alicuota>' || cast(vtasaiva as numeric(16, 2)) || '</Alicuota>');
                perform importacion.impxml_add(vNroXml, '<Impo>' || cast(vimpo_iva_27 as numeric(15,2)) || '</Impo>');
                perform importacion.impxml_add(vNroXml, '</RenglonConcepto>');
                vimpoaux:= vimpoaux + cast(vimpo_iva_27 as numeric(15,2));
                if vsigno < 0 then
                   perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Entrada">');
                 else
                   perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Salida">');
                end if; 
                perform importacion.impxml_add(vNroXml, '<IdCtb>' || vidctbcpra || '</IdCtb>'); 
                perform importacion.impxml_add(vNroXml, '<ImpocIva>' || cast(vimpo_netog_27 as numeric(15,2)) + cast(vimpo_iva_27 as numeric(15,2))|| '</ImpocIva>'); 
                perform importacion.impxml_add(vNroXml, '<Impo>' || cast(vimpo_netog_27 as numeric(15,2)) || '</Impo>'); 
                perform importacion.impxml_add(vNroXml, '<Piva>' || cast(vtasaiva as numeric(16, 2)) || '</Piva>');
                perform importacion.impxml_add(vNroXml, '</RenglonContable>');
                vimpoaux:= vimpoaux + cast(vimpo_netog_27 as numeric(15,2));
                vivacalculado:= round((cast(vimpo_netog_27 as numeric(15,2)) * vtasaiva / 100), 2);
                vivadif:= vivadif + (vivacalculado - cast(vimpo_iva_27 as numeric(15,2)));

           end if;
        end if;

-- impo exento
        if cast(vimpoexentas as numeric(16,2)) <> 0.00 then
           if vsigno < 0 then
              perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Entrada">');
            else
           end if; 
           perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Salida">');
           perform importacion.impxml_add(vNroXml, '<IdCtb>' || vidctbcpra || '</IdCtb>'); 
           perform importacion.impxml_add(vNroXml, '<Impo>' || cast(vimpoexentas as numeric(15,2)) || '</Impo>'); 
           perform importacion.impxml_add(vNroXml, '<Piva>' || cast(0.00 as numeric(16, 2)) || '</Piva>');
           perform importacion.impxml_add(vNroXml, '</RenglonContable>');
           vimpoaux:= vimpoaux + cast(vimpoexentas as numeric(15,2));
        end if;

--impuestos provinciales
        if cast(vimpoiibb as numeric(16,2)) <> 0.00 then
           if vsigno < 0 then
              perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Entrada">');
            else
              perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Salida">');
           end if; 
           perform importacion.impxml_add(vNroXml, '<IdCtb>' || vidctbiibb || '</IdCtb>'); 
           perform importacion.impxml_add(vNroXml, '<Impo>' || cast(vimpoiibb as numeric(15,2)) || '</Impo>'); 
           perform importacion.impxml_add(vNroXml, '</RenglonContable>');
           vimpoaux:= vimpoaux + cast(vimpoiibb as numeric(15,2));
        end if;

--impuestos nacionales
        if cast(vimpoinac as numeric(16,2)) <> 0.00 then
           if vsigno < 0 then
              perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Entrada">');
            else
              perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Salida">');
           end if; 
           perform importacion.impxml_add(vNroXml, '<IdCtb>' || vidctbinac || '</IdCtb>'); 
           perform importacion.impxml_add(vNroXml, '<Impo>' || cast(vimpoinac as numeric(15,2)) || '</Impo>'); 
           perform importacion.impxml_add(vNroXml, '</RenglonContable>');
           vimpoaux:= vimpoaux + cast(vimpoinac as numeric(15,2));
        end if;

--impuestos municipales
        if cast(vimpoimun as numeric(16,2)) <> 0.00 then
           if vsigno < 0 then
               perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Entrada">');
             else
               perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Salida">');
           end if; 
           perform importacion.impxml_add(vNroXml, '<IdCtb>' || vidctbimun || '</IdCtb>'); 
           perform importacion.impxml_add(vNroXml, '<Impo>' || cast(vimpoimun as numeric(15,2)) || '</Impo>'); 
           perform importacion.impxml_add(vNroXml, '</RenglonContable>');
           vimpoaux:= vimpoaux + cast(vimpoimun as numeric(15,2));
        end if;

--iva no categorizado 
        if cast(vimponocateg as numeric(16,2)) <> 0.00 then
           if vsigno < 0 then
               perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Entrada">');
             else
               perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Salida">');
           end if; 
           perform importacion.impxml_add(vNroXml, '<IdCtb>' || vidctbimun || '</IdCtb>'); 
           perform importacion.impxml_add(vNroXml, '<Impo>' || cast(vimponocateg as numeric(15,2)) || '</Impo>'); 
           perform importacion.impxml_add(vNroXml, '</RenglonContable>');
           vimpoaux:= vimpoaux + cast(vimponocateg as numeric(15,2));
        end if;


--impuestos internos
        if cast(vimpoiint as numeric(16,2)) <> 0.00 then
           if vsigno < 0 then
              perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Entrada">');
            else
              perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Salida">');
           end if; 
           perform importacion.impxml_add(vNroXml, '<IdCtb>' || vidctbiint || '</IdCtb>'); 
           perform importacion.impxml_add(vNroXml, '<Impo>' || cast(vimpoiint as numeric(15,2)) || '</Impo>'); 
           perform importacion.impxml_add(vNroXml, '</RenglonContable>');
           vimpoaux:= vimpoaux + cast(vimpoiint as numeric(15,2));
        end if;

--otras percepciones
        if cast(vimpotrasper as numeric(16,2)) <> 0.00 then
           if vsigno < 0 then
              perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Entrada">');
            else
              perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Salida">');
           end if; 
           perform importacion.impxml_add(vNroXml, '<IdCtb>' || vidctbotrasper || '</IdCtb>'); 
           perform importacion.impxml_add(vNroXml, '<Impo>' || cast(vimpootrasper as numeric(15,2)) || '</Impo>'); 
           perform importacion.impxml_add(vNroXml, '</RenglonContable>');
           vimpoaux:= vimpoaux + cast(vimpootrasper as numeric(15,2));
        end if;

-- Se van acumulando los distintos importes a medida que se procesan. Luego si hubiera una diferencia entre el total que se informa
-- y el acumulado que se calculo, se agrega un renglón,con el signo correspondiente,  con la imputacion de la compra.  
       if vtotal <> vimpoaux then
           vdife:= abs(vtotal - vimpoaux);
           vsigno:= vsignoori;
           if vtotal < vimpoaux then
              vsigno:= -1 * vsigno;
           end if;    
           if vsigno < 0 then
              perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Entrada">');
            else
              perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Salida">');
           end if;
           perform importacion.impxml_add(vNroXml, '<IdCtb>' || vidctbcpra || '</IdCtb>'); 
           perform importacion.impxml_add(vNroXml, '<ImpocIva>' || cast((vdife) as numeric(15,2)) || '</ImpocIva>'); 
           perform importacion.impxml_add(vNroXml, '<Impo>' || cast((vdife) as numeric(15,2)) || '</Impo>'); 
           perform importacion.impxml_add(vNroXml, '<Piva>' || cast('0.00' as numeric(16, 2)) || '</Piva>');
           perform importacion.impxml_add(vNroXml, '</RenglonContable>');
           vsigno:=vsignoori;
       end if;    
         
-- De forma similar, amedida que se procesan los distintos ivas, se va además calculando y acumulando el iva de ese renglón en base 
-- al neto correspondiente. Si hay diferencia entre el iva informado y el calculado se genera un renglón con el signo que corresponda y 
-- en la cuenta correspondiente a compras 
       if vivadif <> 0 then
           if vivadif > 0 then 
              if vsigno < 0 then
                 perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Salida">');
               else
                 perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Entrada">');
              end if;
            else   
              if vsigno < 0 then
                 perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Entrada">');
               else
                 perform importacion.impxml_add(vNroXml, '<RenglonContable Signo = "Salida">');
              end if;
           end if;
           perform importacion.impxml_add(vNroXml, '<IdCtb>' || vidctbcpra || '</IdCtb>'); 
           perform importacion.impxml_add(vNroXml, '<ImpocIva>' || cast((vivadif) as numeric(15,2)) || '</ImpocIva>'); 
           perform importacion.impxml_add(vNroXml, '<Impo>' || cast((abs(vivadif)) as numeric(15,2)) || '</Impo>'); 
           perform importacion.impxml_add(vNroXml, '<Piva>' || cast('0.00' as numeric(16, 2)) || '</Piva>');
           perform importacion.impxml_add(vNroXml, '</RenglonContable>');
       end if;

       perform importacion.impxml_add(vNroXml, '</Renglones>');
       perform importacion.impxml_add(vNroXml, '</Comprobante>');

    end if; -- vbueno 0 'S'

    -- end if;   

END LOOP;
perform importacion.impxml_add(vNroXml, '</Movimientos>');
CLOSE ccompras;

select count(*) from afip.portaliva_ventas into vcont where tipocomges is null; 
if vcont <> 0 then
   RAISE NOTICE 'Hay comprobantes sin equivalencias fiscales';
end if;   

--Retornar el nroXML del xml generado
RETURN vNroXml;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION afip.portaliva_ventas(character varying, integer, integer, character varying, integer)
  OWNER TO abacouser;


-------------------------------------------------------------------------------------------------------------
SELECT aaVersion('502.04','502.03');    
-------------------------------------------------------------------------------------------------------------

